package io.xccit.hotelsystem.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * SQLite数据库助手类
 */
public class SQLiteHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "hotel.db";
    private static final int DATABASE_VERSION = 3;
    
    private static final String TABLE_HOTELS = "hotels";
    private static final String COLUMN_HOTEL_ID = "id";
    private static final String COLUMN_HOTEL_NAME = "name";
    private static final String COLUMN_HOTEL_DESC = "description";
    private static final String COLUMN_HOTEL_PRICE = "price";
    private static final String COLUMN_HOTEL_IMAGE = "image";
    
    private static final String TABLE_ORDERS = "orders";
    private static final String COLUMN_ORDER_ID = "id";
    private static final String COLUMN_ORDER_HOTEL_NAME = "hotel_name";
    private static final String COLUMN_START_TIME = "start_time";
    private static final String COLUMN_END_TIME = "end_time";
    private static final String COLUMN_ORDER_STATUS = "order_status";
    private static final String COLUMN_PRICE = "price";
    private static final String COLUMN_ORDER_HOTEL_IMAGE = "hotel_image";
    private static final String COLUMN_ORDER_NAME = "name";
    private static final String COLUMN_ORDER_PHONE = "phone";
    
    private static final String TABLE_USERS = "user";
    private static final String COLUMN_USERNAME = "username";
    private static final String COLUMN_PASSWORD = "password";

    public SQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_USERS_TABLE = "CREATE TABLE " + TABLE_USERS + "("
                + COLUMN_USERNAME + " TEXT PRIMARY KEY,"
                + COLUMN_PASSWORD + " TEXT" + ")";
        db.execSQL(CREATE_USERS_TABLE);
        
        String CREATE_ORDERS_TABLE = "CREATE TABLE " + TABLE_ORDERS + "("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_USERNAME + " TEXT,"
                + COLUMN_ORDER_HOTEL_NAME + " TEXT,"
                + COLUMN_START_TIME + " TEXT,"
                + COLUMN_END_TIME + " TEXT,"
                + COLUMN_ORDER_STATUS + " INTEGER,"
                + COLUMN_PRICE + " REAL,"
                + COLUMN_HOTEL_IMAGE + " TEXT,"
                + COLUMN_ORDER_NAME + " TEXT,"
                + COLUMN_ORDER_PHONE + " TEXT,"
                + COLUMN_ORDER_HOTEL_IMAGE + " TEXT,"
                + "FOREIGN KEY(" + COLUMN_USERNAME + ") REFERENCES " + TABLE_USERS + "(" + COLUMN_USERNAME + ")"
                + ")";
        db.execSQL(CREATE_ORDERS_TABLE);
        
        String CREATE_HOTELS_TABLE = "CREATE TABLE " + TABLE_HOTELS + "("
                + COLUMN_HOTEL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_HOTEL_NAME + " TEXT,"
                + COLUMN_HOTEL_DESC + " TEXT,"
                + COLUMN_HOTEL_PRICE + " TEXT,"
                + COLUMN_HOTEL_IMAGE + " TEXT" + ")";
        db.execSQL(CREATE_HOTELS_TABLE);
        
        // 添加一个测试用户
        ContentValues userValues = new ContentValues();
        userValues.put(COLUMN_USERNAME, "admin");
        userValues.put(COLUMN_PASSWORD, "123456");
        db.insert(TABLE_USERS, null, userValues);
        
        // 添加两条测试订单
        ContentValues orderValues1 = new ContentValues();
        orderValues1.put(COLUMN_USERNAME, "admin");
        orderValues1.put(COLUMN_ORDER_HOTEL_NAME, "豪华大酒店");
        orderValues1.put(COLUMN_START_TIME, "2023-10-01");
        orderValues1.put(COLUMN_END_TIME, "2023-10-05");
        orderValues1.put(COLUMN_ORDER_STATUS, 1);
        orderValues1.put(COLUMN_PRICE, 1200.00);
        orderValues1.put(COLUMN_HOTEL_IMAGE, "hotel1.jpg");
        orderValues1.put(COLUMN_ORDER_NAME, "小昕");
        orderValues1.put(COLUMN_ORDER_PHONE, "18293888946");
        db.insert(TABLE_ORDERS, null, orderValues1);

        ContentValues orderValues2 = new ContentValues();
        orderValues2.put(COLUMN_USERNAME, "admin");
        orderValues2.put(COLUMN_ORDER_HOTEL_NAME, "温馨客栈");
        orderValues2.put(COLUMN_START_TIME, "2023-11-15");
        orderValues2.put(COLUMN_END_TIME, "2023-11-20");
        orderValues2.put(COLUMN_ORDER_STATUS, 3);
        orderValues2.put(COLUMN_PRICE, 800.00);
        orderValues2.put(COLUMN_HOTEL_IMAGE, "hotel2.jpg");
        orderValues2.put(COLUMN_ORDER_NAME, "小昕");
        orderValues2.put(COLUMN_ORDER_PHONE, "18293888946");
        db.insert(TABLE_ORDERS, null, orderValues2);
        
        // 添加测试酒店数据
        addTestHotels(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_ORDERS);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_HOTELS);
            String CREATE_ORDERS_TABLE = "CREATE TABLE " + TABLE_ORDERS + "("
                    + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + COLUMN_USERNAME + " TEXT,"
                    + COLUMN_ORDER_HOTEL_NAME + " TEXT,"
                    + COLUMN_START_TIME + " TEXT,"
                    + COLUMN_END_TIME + " TEXT,"
                    + COLUMN_ORDER_STATUS + " INTEGER,"
                    + COLUMN_PRICE + " REAL,"
                    + COLUMN_ORDER_HOTEL_IMAGE + " TEXT,"
                    + COLUMN_ORDER_NAME + " TEXT,"
                    + COLUMN_ORDER_PHONE + " TEXT,"
                    + "FOREIGN KEY(" + COLUMN_USERNAME + ") REFERENCES " + TABLE_USERS + "(" + COLUMN_USERNAME + ")"
                    + ")";
            db.execSQL(CREATE_ORDERS_TABLE);
        
        String CREATE_HOTELS_TABLE = "CREATE TABLE " + TABLE_HOTELS + "("
                + COLUMN_HOTEL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_HOTEL_NAME + " TEXT,"
                + COLUMN_HOTEL_DESC + " TEXT,"
                + COLUMN_HOTEL_PRICE + " TEXT,"
                + COLUMN_HOTEL_IMAGE + " TEXT" + ")";
        db.execSQL(CREATE_HOTELS_TABLE);
            
            // 添加两条测试订单
            ContentValues orderValues1 = new ContentValues();
            orderValues1.put(COLUMN_USERNAME, "admin");
            orderValues1.put(COLUMN_ORDER_HOTEL_NAME, "豪华大酒店");
            orderValues1.put(COLUMN_START_TIME, "2025-05-30");
            orderValues1.put(COLUMN_END_TIME, "2025-06-01");
            orderValues1.put(COLUMN_ORDER_STATUS, 1);
            orderValues1.put(COLUMN_PRICE, 1200.00);
            orderValues1.put(COLUMN_HOTEL_IMAGE, "hotel1.jpg");
            orderValues1.put(COLUMN_ORDER_NAME, "小昕");
            orderValues1.put(COLUMN_ORDER_PHONE, "18293888946");
            db.insert(TABLE_ORDERS, null, orderValues1);
            
            ContentValues orderValues2 = new ContentValues();
            orderValues2.put(COLUMN_USERNAME, "admin");
            orderValues2.put(COLUMN_ORDER_HOTEL_NAME, "温馨客栈");
            orderValues1.put(COLUMN_START_TIME, "2025-05-30");
            orderValues1.put(COLUMN_END_TIME, "2025-06-01");
            orderValues2.put(COLUMN_ORDER_STATUS, 2);
            orderValues2.put(COLUMN_PRICE, 800.00);
            orderValues2.put(COLUMN_HOTEL_IMAGE, "hotel2.jpg");
            orderValues2.put(COLUMN_ORDER_NAME, "小昕");
            orderValues2.put(COLUMN_ORDER_PHONE, "18293888946");
            db.insert(TABLE_ORDERS, null, orderValues2);
            
            // 添加测试酒店数据
            addTestHotels(db);
        }
    }

    /**
     * 用户登录校验
     * @param username
     * @param password
     * @return
     */
    public boolean checkUser(String username, String password) {
        SQLiteDatabase db = this.getReadableDatabase();
        String[] columns = {COLUMN_USERNAME};
        String selection = COLUMN_USERNAME + " = ?" + " AND " + COLUMN_PASSWORD + " = ?";
        String[] selectionArgs = {username, password};

        Cursor cursor = db.query(TABLE_USERS, columns, selection, selectionArgs, null, null, null);
        int count = cursor.getCount();
        cursor.close();
        db.close();

        return count > 0;
    }

    /**
     * 用户名已存在校验
     * @param username
     * @return
     */
    public boolean checkUsernameExists(String username) {
        SQLiteDatabase db = this.getReadableDatabase();
        String[] columns = {COLUMN_USERNAME};
        String selection = COLUMN_USERNAME + " = ?";
        String[] selectionArgs = {username};

        Cursor cursor = db.query(TABLE_USERS, columns, selection, selectionArgs, null, null, null);
        int count = cursor.getCount();
        cursor.close();
        db.close();

        return count > 0;
    }

    /**
     * 添加用户
     * @param username
     * @param password
     */
    public void addUser(String username, String password) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USERNAME, username);
        values.put(COLUMN_PASSWORD, password);
        db.insert(TABLE_USERS, null, values);
        db.close();
    }
    
    private void addTestHotels(SQLiteDatabase db) {
        // 豪华酒店
        ContentValues hotel1 = new ContentValues();
        hotel1.put(COLUMN_HOTEL_NAME, "豪华度假酒店");
        hotel1.put(COLUMN_HOTEL_DESC, "五星级海景度假酒店，提供私人沙滩和SPA服务");
        hotel1.put(COLUMN_HOTEL_PRICE, "1200");
        hotel1.put(COLUMN_HOTEL_IMAGE, "hotel_home_1");
        db.insert(TABLE_HOTELS, null, hotel1);
        
        ContentValues hotel2 = new ContentValues();
        hotel2.put(COLUMN_HOTEL_NAME, "皇家花园酒店");
        hotel2.put(COLUMN_HOTEL_DESC, "城市中心的奢华体验，配备顶级餐厅和泳池");
        hotel2.put(COLUMN_HOTEL_PRICE, "1500");
        hotel2.put(COLUMN_HOTEL_IMAGE, "hotel_home_2");
        db.insert(TABLE_HOTELS, null, hotel2);
        
        // 商务酒店
        ContentValues hotel3 = new ContentValues();
        hotel3.put(COLUMN_HOTEL_NAME, "商务会议中心酒店");
        hotel3.put(COLUMN_HOTEL_DESC, "配备完善会议设施，适合商务出行");
        hotel3.put(COLUMN_HOTEL_PRICE, "800");
        hotel3.put(COLUMN_HOTEL_IMAGE, "hotel_home_3");
        db.insert(TABLE_HOTELS, null, hotel3);
        
        ContentValues hotel4 = new ContentValues();
        hotel4.put(COLUMN_HOTEL_NAME, "城市快捷商务酒店");
        hotel4.put(COLUMN_HOTEL_DESC, "交通便利，提供免费WiFi和办公区");
        hotel4.put(COLUMN_HOTEL_PRICE, "600");
        hotel4.put(COLUMN_HOTEL_IMAGE, "hotel_home_4");
        db.insert(TABLE_HOTELS, null, hotel4);
        
        // 经济酒店
        ContentValues hotel5 = new ContentValues();
        hotel5.put(COLUMN_HOTEL_NAME, "经济型连锁酒店");
        hotel5.put(COLUMN_HOTEL_DESC, "干净舒适的基础住宿选择");
        hotel5.put(COLUMN_HOTEL_PRICE, "300");
        hotel5.put(COLUMN_HOTEL_IMAGE, "hotel_home_1");
        db.insert(TABLE_HOTELS, null, hotel5);
        
        ContentValues hotel6 = new ContentValues();
        hotel6.put(COLUMN_HOTEL_NAME, "青年旅舍");
        hotel6.put(COLUMN_HOTEL_DESC, "背包客的理想选择，共享空间节省开支");
        hotel6.put(COLUMN_HOTEL_PRICE, "150");
        hotel6.put(COLUMN_HOTEL_IMAGE, "hotel_home_2");
        db.insert(TABLE_HOTELS, null, hotel6);
        
        // 快捷酒店
        ContentValues hotel7 = new ContentValues();
        hotel7.put(COLUMN_HOTEL_NAME, "快捷酒店A");
        hotel7.put(COLUMN_HOTEL_DESC, "24小时前台服务，即到即住");
        hotel7.put(COLUMN_HOTEL_PRICE, "180");
        hotel7.put(COLUMN_HOTEL_IMAGE, "hotel_home_3");
        db.insert(TABLE_HOTELS, null, hotel7);
        
        ContentValues hotel8 = new ContentValues();
        hotel8.put(COLUMN_HOTEL_NAME, "快捷酒店B");
        hotel8.put(COLUMN_HOTEL_DESC, "简约风格，经济实惠");
        hotel8.put(COLUMN_HOTEL_PRICE, "180");
        hotel8.put(COLUMN_HOTEL_IMAGE, "hotel_home_4");
        db.insert(TABLE_HOTELS, null, hotel8);
    }

    /**
     * 获取用户订单
     * @param username
     * @return
     */
    public Cursor getUserOrders(String username) {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(TABLE_ORDERS, 
                new String[]{"id", COLUMN_ORDER_HOTEL_NAME, COLUMN_START_TIME, COLUMN_END_TIME, COLUMN_ORDER_STATUS, COLUMN_PRICE, COLUMN_HOTEL_IMAGE},
                COLUMN_USERNAME + "=?", 
                new String[]{username}, 
                null, null, 
                COLUMN_END_TIME + " DESC");
    }
    
    /**
     * 添加酒店
     */
    public void addHotel(String name, String description, String price, String image) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_HOTEL_NAME, name);
        values.put(COLUMN_HOTEL_DESC, description);
        values.put(COLUMN_HOTEL_PRICE, price);
        values.put(COLUMN_HOTEL_IMAGE, image);
        db.insert(TABLE_HOTELS, null, values);
        db.close();
    }
    
    /**
     * 获取所有酒店
     */
    public Cursor getAllHotels() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(TABLE_HOTELS, 
                new String[]{COLUMN_HOTEL_ID, COLUMN_HOTEL_NAME, COLUMN_HOTEL_DESC, COLUMN_HOTEL_PRICE, COLUMN_HOTEL_IMAGE},
                null, null, null, null, null);
    }
    
    /**
     * 根据ID获取酒店
     */
    public Cursor getHotelById(int id) {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(TABLE_HOTELS, 
                new String[]{COLUMN_HOTEL_ID, COLUMN_HOTEL_NAME, COLUMN_HOTEL_DESC, COLUMN_HOTEL_PRICE, COLUMN_HOTEL_IMAGE},
                COLUMN_HOTEL_ID + "=?", 
                new String[]{String.valueOf(id)}, 
                null, null, null);
    }
    
    
    /**
     * 根据ID获取订单
     */
    public Cursor getOrderById(int id) {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(TABLE_ORDERS, 
                new String[]{"id", COLUMN_ORDER_HOTEL_NAME, COLUMN_START_TIME, COLUMN_END_TIME, COLUMN_ORDER_STATUS, COLUMN_PRICE, COLUMN_HOTEL_IMAGE, COLUMN_ORDER_NAME, COLUMN_ORDER_PHONE},
                "id=?", 
                new String[]{String.valueOf(id)}, 
                null, null, null);
    }
    
    /**
     * 更新订单状态
     */
    public void updateOrderStatus(int orderId, int newStatus) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_ORDER_STATUS, newStatus);
        db.update(TABLE_ORDERS, values, "id=?", new String[]{String.valueOf(orderId)});
        db.close();
    }
    
    /**
     * 添加订单
     * @param username 用户名
     * @param hotelName 酒店名称
     * @param startTime 入住时间
     * @param endTime 离店时间
     * @param status 订单状态
     * @param price 价格
     * @param hotelImage 酒店图片
     * @return 插入的行ID
     */
    public long addOrder(String username, String hotelName, String startTime, String endTime, int status, double price, String hotelImage, String name, String phone) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USERNAME, username);
        values.put(COLUMN_ORDER_HOTEL_NAME, hotelName);
        values.put(COLUMN_START_TIME, startTime);
        values.put(COLUMN_END_TIME, endTime);
        values.put(COLUMN_ORDER_STATUS, status);
        values.put(COLUMN_PRICE, price);
        values.put(COLUMN_HOTEL_IMAGE, hotelImage);
        values.put(COLUMN_ORDER_NAME, name);
        values.put(COLUMN_ORDER_PHONE, phone);
        long result = db.insert(TABLE_ORDERS, null, values);
        db.close();
        return result;
    }
}